Updating Records Using UPDATE in MySQL

Course: MySQL Training

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Free Course Demo

1. Introduction to Updating Records in MySQL

In MySQL, the UPDATE statement is used to modify existing records stored in a database table. After data has been inserted into a table, you may need to correct a name, change a price, update a student's marks, modify an employee's salary, or change the status of an order. The UPDATE command allows you to make these changes without deleting the existing record and inserting a new one.

For example, suppose a college database contains a student named Rahul whose email address has changed. Instead of removing the student record and creating another one, you can use UPDATE to change only the email address while preserving the student's ID, name, course, and other information.

The UPDATE statement is a data manipulation language (DML) command. It changes data stored in a table rather than changing the table's structure.

Why Is the UPDATE Statement Important?

• Corrects inaccurate or outdated information.
• Changes values in one or more columns of existing records.
• Updates multiple records that satisfy a condition.
• Supports conditional updates using the WHERE clause.
• Can update values using calculations and expressions.
• Helps maintain accurate business and application data.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand the purpose and syntax of the MySQL UPDATE statement.
• Update a single column in an existing record.
• Update multiple columns in the same record.
• Use the WHERE clause to target specific records.
• Update multiple records using conditions.
• Use comparison operators and logical operators in update conditions.
• Perform calculated updates using expressions.
• Understand the risks of updating records without a condition.
• Verify changes using SELECT statements.
• Use transactions and safe practices when modifying important data.

3. Understanding the UPDATE Statement

The UPDATE statement changes the values of one or more columns in records that match a specified condition. The table structure remains unchanged; only the selected data values are modified.

Basic Syntax

UPDATE table_name
SET column_name = new_value
WHERE condition;

Explanation of the Syntax

Important: The WHERE clause is usually essential when you intend to update only selected records. If you omit it, MySQL can update every row in the table.

4. Preparing a Sample Database

Before practising update queries, create a database and a table containing sample student records. The following examples use a table named students.

Step 1: Create a Database

CREATE DATABASE college_db;
USE college_db;

The CREATE DATABASE statement creates the database, and USE selects it for subsequent operations.

Step 2: Create the Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    course VARCHAR(50),
    marks DECIMAL(5,2),
    city VARCHAR(50),
    email VARCHAR(100),
    status VARCHAR(20)
);

Step 3: Insert Sample Records

INSERT INTO students
(student_id, student_name, course, marks, city, email, status)
VALUES
(1, 'Rahul Sharma', 'MySQL', 78.50, 'Mumbai', '[email protected]', 'Active'),
(2, 'Priya Patel', 'Python', 85.00, 'Pune', '[email protected]', 'Active'),
(3, 'Amit Verma', 'Java', 69.50, 'Delhi', '[email protected]', 'Active'),
(4, 'Sneha Singh', 'MySQL', 92.00, 'Mumbai', '[email protected]', 'Active'),
(5, 'Karan Mehta', 'Python', 55.00, 'Nashik', '[email protected]', 'Inactive');

Step 4: View the Existing Records

SELECT * FROM students;

Run this query before performing updates so you can compare the original values with the modified values later.

5. Updating a Single Column

You can update one column by specifying the table, the column to change, the new value, and a condition that identifies the correct record.

Example: Change a Student's City

UPDATE students
SET city = 'Thane'
WHERE student_id = 1;

This query changes the city of the student whose ID is 1 to Thane. Other columns in that record remain unchanged.

Verify the Updated Record

SELECT * FROM students
WHERE student_id = 1;

Always verify important updates with a SELECT query to confirm that the intended record was modified.

Example: Update an Email Address

UPDATE students
SET email = '[email protected]'
WHERE student_id = 1;

This statement updates the email address for student ID 1 without changing the student's name, course, marks, city, or status.

6. Updating Multiple Columns

The UPDATE statement can change multiple columns in a single query. Separate each column assignment in the SET clause with a comma.

Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    column3 = value3
WHERE condition;

Example: Update a Student's Course and Marks

UPDATE students
SET course = 'Advanced MySQL',
    marks = 88.00
WHERE student_id = 2;

This query changes both the course and marks for student ID 2. Because the assignments are in the same statement, they are applied as part of the same update operation.

Example: Update City, Email, and Status

UPDATE students
SET city = 'Mumbai',
    email = '[email protected]',
    status = 'Active'
WHERE student_id = 2;

Use multiple-column updates when several related changes need to be made to the same record.

7. Using WHERE to Update Specific Records

The WHERE clause identifies the rows that should be changed. A carefully written condition prevents unintended modifications to unrelated records.

Example: Update by Primary Key

UPDATE students
SET status = 'Inactive'
WHERE student_id = 3;

Since student_id is the primary key, this condition identifies at most one student record.

Example: Update Records Based on a Course

UPDATE students
SET status = 'Course Review'
WHERE course = 'MySQL';

This query changes the status of every student whose course value is exactly MySQL. It may update more than one row.

Example: Update Records Based on a City

UPDATE students
SET status = 'City Verified'
WHERE city = 'Mumbai';

Every row matching the city condition is eligible for the update. Before executing this query, inspect the matching records to ensure that all of them should receive the same change.

Preview Matching Rows Before Updating

SELECT *
FROM students
WHERE city = 'Mumbai';

A useful habit is to run the equivalent SELECT condition first. This lets you review the rows that the update is expected to affect.

8. Updating Multiple Records

An update can modify several records at once. MySQL evaluates the condition and updates each row that matches it.

Example: Increase Marks for All MySQL Students

UPDATE students
SET marks = marks + 5
WHERE course = 'MySQL';

This query adds five marks to every student whose course is MySQL. It does not add five marks to students in other courses.

Example: Set a Common Status for Low-Marking Students

UPDATE students
SET status = 'Needs Improvement'
WHERE marks < 60;

All students with marks below 60 receive the new status. The marks themselves remain unchanged.

Example: Update Records Matching Two Conditions

UPDATE students
SET status = 'Review Required'
WHERE course = 'Python'
  AND marks < 60;

Both conditions must be true for a row to be updated. This query selects Python students whose marks are below 60.

9. Using Comparison Operators in UPDATE

Comparison operators help select records according to numerical or textual conditions. They are often used with marks, salaries, prices, dates, and status values.

Example: Update High-Scoring Students

UPDATE students
SET status = 'Excellent'
WHERE marks >= 90;

This query sets the status of students with marks of 90 or above to Excellent.

Example: Update Students Who Are Not Active

UPDATE students
SET status = 'Follow Up'
WHERE status <> 'Active';

The query matches rows whose status is not equal to Active. Remember that comparisons involving NULL require special handling because NULL represents a missing or unknown value.

10. Using AND and OR in UPDATE

Logical operators combine multiple conditions to control which records are updated.

Using AND

AND requires all specified conditions to be true.

UPDATE students
SET status = 'Eligible'
WHERE course = 'MySQL'
  AND marks >= 75;

This updates only students enrolled in MySQL who have marks of at least 75.

Using OR

OR requires at least one specified condition to be true.

UPDATE students
SET status = 'Special Review'
WHERE city = 'Mumbai'
   OR marks >= 90;

This query updates students who are in Mumbai, students with marks of at least 90, or students who meet both conditions.

Combining AND, OR, and Parentheses

UPDATE students
SET status = 'Priority Review'
WHERE (course = 'MySQL' OR course = 'Python')
  AND marks < 70;

Parentheses make the intended logic clear. This query targets students taking MySQL or Python who have marks below 70.

11. Updating Values Using Arithmetic Expressions

You can use arithmetic expressions in the SET clause to calculate new values from existing values. This is useful for salary increments, price adjustments, stock changes, and mark corrections.

Increase Marks by a Fixed Amount

UPDATE students
SET marks = marks + 3
WHERE student_id = 3;

The new marks are calculated by adding three to the student's existing marks.

Decrease Marks by a Fixed Amount

UPDATE students
SET marks = marks - 2
WHERE student_id = 3;

This subtracts two from the existing marks. Use this type of update only when the adjustment is justified and permitted by the application's rules.

Apply a Percentage Increase

UPDATE students
SET marks = marks * 1.10
WHERE student_id = 3;

This calculates marks that are 10% higher than the existing value. Before using percentage-based updates, verify the required rounding rules and maximum permitted marks for your application.

Example: Salary Increment in an Employee Table

UPDATE employees
SET salary = salary * 1.08
WHERE department = 'IT';

Assuming an employees table has a numeric salary column and a department column, this query increases salaries in the IT department by 8%. The table and column names must match your actual database schema.

12. Updating Records with IN and BETWEEN

Using IN

The IN operator matches a value against a list of possible values.

UPDATE students
SET status = 'Course Updated'
WHERE course IN ('MySQL', 'Python');

This updates records whose course is either MySQL or Python.

Using BETWEEN

The BETWEEN operator selects values within an inclusive range.

UPDATE students
SET status = 'Average'
WHERE marks BETWEEN 60 AND 75;

The condition includes marks equal to 60 and marks equal to 75, as well as values between them.

13. Updating Records with LIKE

The LIKE operator is used for pattern matching in text columns. It can identify records by a partial name, email pattern, or other text pattern.

Example: Update Students Whose Names Start with R

UPDATE students
SET status = 'Name Verified'
WHERE student_name LIKE 'R%';

The percent sign (%) matches zero or more characters. Therefore, 'R%' matches names beginning with R, subject to the column's collation and case-sensitivity rules.

Example: Update Email Addresses from a Particular Domain

UPDATE students
SET status = 'Email Review'
WHERE email LIKE '%@example.com';

This selects email values ending in @example.com. Pattern matching is useful when a condition depends on part of a text value rather than the entire value.

14. Updating NULL Values

In MySQL, NULL represents a missing or unknown value. Use IS NULL or IS NOT NULL in conditions instead of using = NULL or != NULL.

Example: Fill Missing Email Addresses with a Placeholder

UPDATE students
SET email = '[email protected]'
WHERE email IS NULL;

This changes only records whose email value is NULL. A placeholder email should not be treated as a real, verified address.

Example: Update Records with a Known Email

UPDATE students
SET status = 'Email Available'
WHERE email IS NOT NULL;

This updates records where an email value exists. If blank strings are also possible in your data, remember that an empty string is different from NULL.

15. Updating Dates and Date-Related Values

Date columns can also be updated. Use an appropriate date format and make sure the new value is valid for the column's data type.

Example: Update an Employee Joining Date

UPDATE employees
SET joining_date = '2026-01-15'
WHERE employee_id = 101;

This example assumes that the employees table contains a joining_date column and an employee_id column.

Example: Update Orders Within a Date Range

UPDATE orders
SET order_status = 'Under Review'
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

This selects orders whose date values fall within the specified inclusive range. For DATETIME or TIMESTAMP columns, carefully account for time portions when defining date ranges.

16. Updating Records with a JOIN

MySQL supports multi-table update syntax, which can update records in one table using related data from another table. This is useful when two tables are connected through a common key.

Example: Update Employee Department Information

UPDATE employees AS e
JOIN departments AS d
  ON e.department_id = d.department_id
SET e.department_name = d.department_name
WHERE d.department_id = 2;

This example assumes that both tables contain the required columns. It updates the department name in matching employee records using the corresponding department record. Review the join condition carefully to avoid matching the wrong rows.

Important Considerations for JOIN Updates

• Confirm that the join condition correctly connects related records.
• Check for duplicate matches that could make the intended result unclear.
• Use table aliases to make column references easier to understand.
• Run a corresponding SELECT query with the same join and filter before updating.
• Back up important data and test complex updates in a safe environment.

17. What Happens When WHERE Is Omitted?

If an UPDATE statement has no WHERE clause, MySQL can update every row in the specified table. This is one of the most common and serious mistakes when modifying data.

Example of a Risky Query

UPDATE students
SET status = 'Inactive';

This query sets the status of all rows in the students table to Inactive. It is appropriate only when every row is intentionally supposed to receive that value.

Safer Version for a Specific Student

UPDATE students
SET status = 'Inactive'
WHERE student_id = 5;

The condition limits the update to the record with student ID 5.

Checklist Before Running an UPDATE

• Identify the correct database and table.
• Confirm the column names and intended new values.
• Write a precise WHERE condition when updating selected rows.
• Run a SELECT query with the same condition to inspect matching rows.
• Check whether the update is expected to affect one row or many rows.
• Use a transaction when supported and appropriate for the storage engine and workflow.
• Verify the result after the update.

18. Understanding UPDATE and Transactions

A transaction groups database operations so that they can be committed together or rolled back when necessary. Transactions are especially useful when a change must be checked before it becomes permanent. Transaction support depends on the storage engine and the operations involved; InnoDB is the usual transactional storage engine in MySQL.

Example: Update and Verify Before Committing

START TRANSACTION;

UPDATE students
SET marks = marks + 2
WHERE student_id = 3;

SELECT *
FROM students
WHERE student_id = 3;

COMMIT;

In this example, the update is made within a transaction, and the affected row is inspected before the transaction is committed. If you discover an error before committing, you can use ROLLBACK instead of COMMIT, provided the changes are still part of the active transaction and the operations are transactional.

Example: Roll Back an Unwanted Update

START TRANSACTION;

UPDATE students
SET status = 'Inactive'
WHERE student_id = 2;

SELECT *
FROM students
WHERE student_id = 2;

ROLLBACK;

This example demonstrates cancelling the uncommitted update. After a successful rollback, the change made within the transaction is undone. Do not assume that a rollback can reverse a transaction after it has already been committed.

COMMIT vs ROLLBACK

19. UPDATE with Safe Update Mode

Some MySQL client tools, including MySQL Workbench configurations, can enable safe update behavior. Depending on the client settings and session configuration, a broad update without a suitable key-based condition may be rejected to reduce the risk of accidental changes.

Example of a Key-Based Update

UPDATE students
SET status = 'Active'
WHERE student_id = 4;

This uses the primary key to identify the intended row.

Checking the Session Setting

SELECT @@SQL_SAFE_UPDATES;

The result indicates whether the session's safe-update setting is enabled. Do not disable safety protections just to force a risky query to run. Instead, review the condition, inspect the target rows, and use a controlled workflow.

20. Checking the Result of an UPDATE

After running an update, verify the stored values and review the result reported by your MySQL client. Depending on client settings and the statement, the reported affected-row information may distinguish rows actually changed from rows matched.

Use SELECT to Confirm the Change

UPDATE students
SET city = 'Navi Mumbai'
WHERE student_id = 4;

SELECT student_id, student_name, city
FROM students
WHERE student_id = 4;

Review the Table After an Update

SELECT *
FROM students
ORDER BY student_id;

This displays all records in primary-key order, making it easier to inspect the current table contents.

Inspect the Number of Matching Rows First

SELECT COUNT(*) AS matching_rows
FROM students
WHERE course = 'MySQL';

This query counts the records matching a condition before you run an update that uses the same condition. It is particularly helpful when you expect to update a specific number of rows.

21. Common UPDATE Errors and How to Fix Them

22. Best Practices for Using UPDATE

• Use a WHERE clause carefully: Always specify which records should change unless you intentionally need to update every row.
• Prefer primary keys for individual records: A unique identifier usually makes the target record unambiguous.
• Preview the target records: Run a SELECT statement with the same condition before an important update.
• Check the affected-row count: Compare the result with the number of rows you expected to change.
• Use transactions when appropriate: Transactions provide a way to review and cancel uncommitted changes in transactional tables.
• Back up important data: Keep a suitable backup or recovery strategy for valuable production records.
• Validate new values: Make sure values comply with column types, constraints, and application rules.
• Avoid unnecessary changes: Update only the columns that actually need modification.
• Use clear conditions: Parentheses can make complex combinations of AND and OR easier to understand.
• Test in a development environment: Validate complex or large updates before using them in production.
• Use parameterized queries in applications: Parameterization helps separate SQL instructions from user-provided values and reduces SQL injection risk.
• Follow access controls: Give applications and users only the database permissions they require.

23. Practical Project: Updating a Student Database

In this practical exercise, you will update several fields in the sample students table and verify each result.

Task 1: Update a Student's Email

UPDATE students
SET email = '[email protected]'
WHERE student_id = 3;

Expected result: The email address for student ID 3 is changed.

Task 2: Update Course and Marks

UPDATE students
SET course = 'Advanced Java',
    marks = 74.00
WHERE student_id = 3;

Expected result: The course and marks are changed for student ID 3.

Task 3: Increase Marks for MySQL Students

UPDATE students
SET marks = marks + 2
WHERE course = 'MySQL';

Expected result: Every row that still has the course value MySQL receives an increase of two marks. Check the table's current data first because earlier exercises may have changed it.

Task 4: Mark Students with Low Marks

UPDATE students
SET status = 'Needs Improvement'
WHERE marks < 60;

Expected result: Students with marks below 60 receive the specified status.

Task 5: Verify the Results

SELECT student_id, student_name, course, marks, city, email, status
FROM students
ORDER BY student_id;

Review the displayed values and confirm that only the intended records and columns were modified.

Task 6: Document Your Changes

1. Record the original value of each field you intend to update.
2. Write the update query and identify its target condition.
3. Preview the matching rows with a SELECT statement.
4. Execute the update in your practice database.
5. Verify the new values with another SELECT statement.
6. Record the number of rows that were expected to change and the result reported by MySQL.

24. Frequently Asked Interview Questions

Q1. What is the UPDATE statement in MySQL?

The UPDATE statement modifies existing records in a table. It can change one or more column values in rows that match a specified condition.

Q2. What is the purpose of the SET clause?

The SET clause specifies the columns to modify and the new values or expressions to assign to them.

Q3. Why is the WHERE clause important in UPDATE?

The WHERE clause limits the update to matching records. Without it, every row in the target table may be updated.

Q4. Can UPDATE modify multiple columns at once?

Yes. Multiple assignments can be included in one SET clause, separated by commas.

Q5. Can one UPDATE statement modify multiple rows?

Yes. Every row that matches the condition can be updated by the same statement.

Q6. What happens if no rows match the WHERE condition?

No records are modified. The MySQL client reports the statement result, which can help you determine whether the condition matched any rows.

Q7. Can UPDATE use arithmetic expressions?

Yes. For example, SET marks = marks + 5 adds five to the existing marks value for each matching row.

Q8. What is the difference between UPDATE and INSERT?

INSERT adds new records, while UPDATE modifies existing records.

Q9. How can you update only one record?

Use a condition based on a unique identifier, such as a primary key, to identify the intended row.

Q10. How do you update a NULL value?

Use IS NULL in the WHERE condition to select rows with missing values, and use SET to assign the required replacement value.

Q11. What are COMMIT and ROLLBACK?

COMMIT saves changes in the current transaction, while ROLLBACK cancels uncommitted transactional changes.

Q12. How can you verify an UPDATE statement?

Run a SELECT statement with an appropriate condition to inspect the updated records and confirm their values.

25. Practice Questions

1. Write a query to change the city of student ID 1 to Pune.
2. Write a query to update the email address of student ID 4.
3. Write a query to change the course and marks of student ID 2 in one statement.
4. Write a query to increase marks by five for students enrolled in MySQL.
5. Write a query to set the status to Excellent for students who have marks of 90 or above.
6. Write a query to set the status to Needs Improvement for students whose marks are below 60.
7. Write a query to update records where the city is either Mumbai or Pune.
8. Write a query to update students whose names start with the letter S.
9. Write a query to replace missing email values with a placeholder.
10. Explain what happens when you run an UPDATE statement without a WHERE clause.
11. Explain why you should preview the target rows before a large update.
12. Demonstrate how to use a transaction to inspect an update and then roll it back.

26. Quick Revision

• UPDATE modifies existing records.
• SET defines the new column values.
• WHERE selects the rows that should change.
• Multiple columns can be changed in a single statement.
• One statement can update multiple matching records.
• Arithmetic expressions can calculate updated values.
• Use IS NULL to match missing values.
• Use SELECT to preview target rows and verify the outcome.
• Use transactions when appropriate to control uncommitted changes.
• Always review conditions carefully to avoid unintended bulk updates.

27. Official MySQL Documentation

For additional information about update syntax and supported options, refer to the official MySQL documentation:

• MySQL UPDATE Statement Documentation
• MySQL Transaction Control: COMMIT and ROLLBACK
• InnoDB Transaction Model

28. Conclusion

The MySQL UPDATE statement is an essential command for maintaining accurate database records. It allows you to change one or multiple columns, target individual rows using primary keys, update groups of records using conditions, and calculate new values from existing data. You can also combine update conditions with comparison operators, logical operators, pattern matching, and joins.

The most important rule is to verify which records will be affected before executing an update. Use precise WHERE conditions, preview matching rows, validate the result, and consider transactions or backups when working with important data. Practising these techniques with the sample student database will help you update MySQL records accurately and safely.

Continue learning: Visit the JustAcademy MySQL Training Course for course details, or register for a course demo to explore the training.